﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace _4m.Utils
{
    class ExcelUtils
    {
        /// <summary>
        /// 把Excel文件中指定的sheet内容读取到DataSet中
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="sheetName">Sheet名称</param>
        /// <returns>Excel的sheet的数据集</returns>
        public static DataSet LoadDataFromExcel(string filePath, string sheetName)
        {
            try
            {
                string strConn =
                    string.Format(
                        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=False;IMEX=1'",
                        filePath);
                using (OleDbConnection oleConn = new OleDbConnection(strConn))
                {
                    oleConn.Open();
                    string sql = string.Format("SELECT * FROM [{0}$]", sheetName);

                    var oleDaExcel = new OleDbDataAdapter(sql, oleConn);
                    var oleDsExcel = new DataSet();
                    oleDaExcel.Fill(oleDsExcel, sheetName);
                    return oleDsExcel;
                }
            }
            catch (Exception exception)
            {
                MessageBox.Show("数据绑定Excel失败！失败原因：" + exception.Message, "提示信息",
                                MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
        }


    }
}
